#!usr/bin/env python
# -*- coding:utf-8 _*-
""" 
@author:robot
@file: tap_connector07_gbase-8s.py
@version:
@time: 2022/01/13 
"""
from jpype import dbapi2
from TapClientConnector.connector.tap_connector00_base import *


class TAPGbase8SConnector(TAPConnectorBase):

    def __init__(self, kwargs):
        super().__init__(kwargs)
        host = self.FORMAT_DB_INFO.get('host')
        port = self.FORMAT_DB_INFO.get('port')
        user = self.FORMAT_DB_INFO.get('user')
        password = self.FORMAT_DB_INFO.get('password')
        database = self.FORMAT_DB_INFO.get('database')
        self.connector = dbapi2.connect(
            dsn=f'jdbc:gbasedbt-sqli://{host}:{port}/{database}:GBASEDBTSERVERS=gbaseserver1;NEWCODESET=UTF8,cp1252,819;DB_LOCALE=zh_CN.utf8;CLIENT_LOCALE=zh_CN.utf8;DBDATE=Y4MD-;delimient=y;',
            driver='com.gbasedbt.jdbc.Driver',
            driver_args={'user': user, 'password': password, },
        )

    def _query(self, sql):
        cursor = self.connector.cursor()
        try:
            cursor.execute(sql)
        except Exception:
            raise SQLError
        res = list(cursor.fetchall())
        # tuple 处理与其他关系数据库一致
        res = [tuple(i) for i in res]
        self.TABLE_DESCRIPTION = [one[0] for one in cursor.description]
        cursor.close()
        return res

    # def _sys_query(self, sql):
    #     cursor = self.connector_sys.cursor()
    #     try:
    #         cursor.execute(sql)
    #     except Exception:
    #         raise SQLError
    #     res = list(cursor.fetchall())
    #     self.table_description = cursor.description
    #     cursor.close()
    #     return res

    @connector_exec_logger
    def _exec(self, sql):
        cursor = self.connector.cursor()
        try:
            cursor.execute(sql)
            self.connector.commit()
        except Exception:
            raise SQLError

    # @connector_query_logger
    # def show_dbs(self):
    #     query_str = "select name from sysdatabases;"
    #     table_list = []
    #     for i in self._sys_query(query_str):
    #         table_list.append(i[0].strip())
    #     return table_list

    @connector_query_logger
    def show_tables(self):
        query_str = "select tabname from systables where tabid >= 100 and tabtype='T'"
        db_list = []
        for i in self._query(query_str):
            db_list.append(i[0])
        return db_list

    # ***** Generate *****

    # ** DDL **

    @generate_logger
    def generate_create(self, table_column, table=None):
        """
        通过表名，字段 类型生成建表语句
        :param table:
        :param table_column:
        :return:
        """
        if table:
            self.CURRENT_TABLE = table
        return f"""
                CREATE TABLE 
               {self.connector_database}.{self.CURRENT_TABLE} 
               {table_column}""".lower()

    @generate_logger
    def generate_drop(self, table=None):
        if table:
            self.CURRENT_TABLE = table
        return f'DROP TABLE {self.CURRENT_TABLE}'.lower()

    # ** DML **

    @generate_logger
    def generate_insert(self, insert, table=None):
        """
        :param table:  table
        :param insert: [columns, values] | [values]
        :return:
        """
        if table:
            self.CURRENT_TABLE = table
        if len(insert) == 1:
            return f"""
                    INSERT INTO
                    {self.connector_database}.{self.CURRENT_TABLE}
                    VALUES ({insert[0]})""".lower()
        if len(insert) == 2:
            return f"""
                    INSERT INTO
                    {self.connector_database}.{self.CURRENT_TABLE} ({insert[0]})
                    VALUES ({insert[1]})""".lower()

    @generate_logger
    def generate_update(self, update, where, table=None):
        """
        :param table:
        :param update: {column: value}
        :param where: WHERE ...
        :return:
        """
        if table:
            self.CURRENT_TABLE = table
        _update_list = [f'{k}={v}' for k, v in update.items()]
        COLUMNS = ",".join(_update_list)
        return f"""
               UPDATE 
               {self.connector_database}.{self.CURRENT_TABLE}
               SET {COLUMNS} {where}""".lower()

    @generate_logger
    def generate_delete(self, where, table=None):
        """
        :param table:
        :param where:
        :return:
        """
        if table:
            self.CURRENT_TABLE = table
        return f"""
                DELETE FROM
                {self.connector_database}.{self.CURRENT_TABLE} {where}
                """.lower()

    @generate_logger
    def generate_select(self, column='*', where='', table=None, order=''):
        if table:
            self.CURRENT_TABLE = table
        return f"""
                SELECT {column} FROM
                {self.connector_database}.{self.CURRENT_TABLE} {where} {order}
                """.lower()


if __name__ == '__main__':
    from TapDataSourceInfo.db_info_pdk import Gbase8s

    T = TAPGbase8SConnector(Gbase8s.AUTO_Gbase8s)
    # # T.exec("insert into test_t1 values(1, 'gbase8s') ")
    print(T.show_tables())

    # T.exec("select * from 'B_MYSQL1658203997201425075'")

    # import jpype
    # from TapClientCore.T01_init_conf.path_conf import PathConf
    # from jpype import dbapi2
    # # 建立连接前需要先启动JVM
    # jpype.addClassPath(PathConf.JAR_GBASE_8S_PATH)
    # jpype.startJVM()
    #
    # # 创建数据库连接
    # conn = dbapi2.connect(
    #     dsn='jdbc:gbasedbt-sqli://192.168.1.189:9088/testdb:GBASEDBTSERVERS=gbaseserver1;NEWCODESET=UTF8,cp1252,819;DB_LOCALE=zh_CN.utf8;CLIENT_LOCALE=zh_CN.utf8;DBDATE=Y4MD-;',
    #     driver='com.gbasedbt.jdbc.Driver',
    #     driver_args={'user': 'gbasedbt', 'password': 'GBase123'}
    # )

    # import sys
    # import jaydebeapi
    #
    # print("\nPython JayDeBeApi JDBC 测试程序开始运行.\n")
    # conn = jaydebeapi.connect("com.gbasedbt.jdbc.Driver",
    #                           "jdbc:gbasedbt-sqli://192.168.1.189:9088/testdb:delimient=y;",
    #                           ["gbasedbt", "GBase123"],
    #                           PathConf.JAR_GBASE_8S_PATH)
    #
    # mycursor = conn.cursor()
    # mycursor.execute("select * from B_MYSQL1658203997201425075")
    # rows = mycursor.fetchall()
    # print(rows)
    # mycursor.close()
    # conn.close()
